package com.yzdbx.utils;

import cn.hutool.core.util.StrUtil;

import java.io.IOException;
import java.io.InputStream;
import java.io.Reader;
import java.nio.charset.StandardCharsets;
import java.sql.*;
import java.util.*;

/**
 * @BelongsProject: SERVEVICE-DATA-GEN
 * @BelongsPackage: com.yzdbx.utils
 * @Author: lk      原始版本            老系统可用
 * @CreateTime: 2025-05-14
 * @Description: TODO
 * @Version: 1.0
 */
@SuppressWarnings("ALL")
public class DbUtil {

    /**
     * 老系统可用
     */
    private static String dbUrl = "jdbc:log4jdbc:mysql://171.35.137.170:3306/whmskj_ZXMY?" +
            "useUnicode=true&characterEncoding=utf-8" +
            "&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";
    private static String dbUser = "root";
    private static String dbPwd = "aht67#@)PTabxc";


//    static {
//        Map<String, String> envMap = System.getenv();
//        try {
//            dbUrl = String.format(dbUrl, envMap.get("DB_HOST"), envMap.get("DB_PORT"), envMap.get("DB_NAME"));
//            dbUser = StrUtil.isEmpty(envMap.get("DB_USER")) ? dbUser : envMap.get("DB_USER");
//            dbPwd = StrUtil.isEmpty(envMap.get("DB_PWD")) ? dbPwd : envMap.get("DB_PWD");
//        } catch (Exception e) {
//            throw new RuntimeException("获取环境变量失败");
//        }
//    }


    /**
     * 获取数据库连接
     *
     * @return
     */
    public static Connection getConnection() {
        Connection conn = null;
        try {
            // 必须显式加载两个驱动类
            Class.forName("net.sf.log4jdbc.DriverSpy");
            Class.forName("com.mysql.cj.jdbc.Driver");

            conn = DriverManager.getConnection(dbUrl, dbUser, dbPwd);

        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("无法连接到【" + dbUrl + "】数据库，请检查数据库是否正常启动！" + "】");
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }

        try {
            conn.setAutoCommit(Boolean.FALSE);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }

        System.out.println("数据库连接成功...");
        return conn;
    }


    public static int getMaxId(Connection connection, String tableName) {
        try {
            PreparedStatement preparedStatement = connection.prepareStatement("select ifnull(max(id),1) from " + tableName);
            ResultSet resultSet = preparedStatement.executeQuery();
            if (resultSet.next())
                return resultSet.getInt(1);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

        return 1;
    }


    public static Map<String, Object> getById(Connection connection, String tableName, Integer id) {

        Map<String, Object> entity = new LinkedHashMap<>();  // 保持插入顺序
        String sql = "SELECT * FROM `" + tableName + "` WHERE id = ?";  // 处理表名反注入

        try (PreparedStatement statement = connection.prepareStatement(sql)) {
            // 参数绑定（预防SQL注入）
            statement.setInt(1, id);

            try (ResultSet rs = statement.executeQuery()) {
                ResultSetMetaData metaData = rs.getMetaData();
                int columnCount = metaData.getColumnCount();

                if (rs.next()) {
                    for (int i = 1; i <= columnCount; i++) {
                        // 动态获取列名和值
                        String columnName = metaData.getColumnLabel(i);  // 兼容别名
                        Object value = rs.getObject(i);
                        entity.put(columnName, value);
                    }
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException("数据库查询失败", e);
        }
        return entity;
    }

    // 根据表名、字段名和值查找一条记录，返回Map<String, Object>
    public static Map<String, Object> getByField(Connection connection, String table, String field, String value) {
        String sql = "SELECT * FROM " + table + " WHERE " + field + " = ?";
        try (PreparedStatement stmt = connection.prepareStatement(sql)) {
            stmt.setString(1, value);
            ResultSet rs = stmt.executeQuery();
            List<LinkedHashMap<String, Object>> list = resultSetToList(rs);
            if (list != null && !list.isEmpty()) {
                return list.get(0);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }


    /**
     * 将ResultSet转换为保留列顺序的Map列表
     *
     * @param resultSet 数据库结果集（调用方需自行关闭资源）
     * @return 有序的Map列表，每个Map对应一行数据
     * @throws SQLException 数据库操作异常
     */
    public static List<LinkedHashMap<String, Object>> resultSetToList(ResultSet resultSet) throws SQLException {
        List<LinkedHashMap<String, Object>> result = new ArrayList<>();

        // 获取结果集元数据
        final ResultSetMetaData metaData = resultSet.getMetaData();
        final int columnCount = metaData.getColumnCount();

        // 预先提取列名（提升性能）
        final String[] columnNames = new String[columnCount];
        for (int i = 0; i < columnCount; i++) {
            // 使用getColumnLabel获取别名（兼容AS语法）
            columnNames[i] = metaData.getColumnLabel(i + 1);
        }

        // 逐行转换数据
        while (resultSet.next()) {
            LinkedHashMap<String, Object> row = new LinkedHashMap<>(columnCount);

            for (int i = 0; i < columnCount; i++) {
                // 使用JDBC 4.1的类型转换
                Object value = resultSet.getObject(i + 1);

                // 特殊类型处理（按需扩展）
                if (value instanceof Blob) {
                    value = blobToString((Blob) value);
                } else if (value instanceof Clob) {
                    value = clobToString((Clob) value);
                }

                row.put(columnNames[i], value);
            }

            result.add(row);
        }

        return result;
    }

    // Blob转字符串工具方法
    private static String blobToString(Blob blob) throws SQLException {
        if (blob == null) return null;
        try (InputStream is = blob.getBinaryStream()) {
            byte[] bytes = is.readAllBytes();
            return new String(bytes, StandardCharsets.UTF_8);
        } catch (IOException e) {
            throw new SQLException("Blob转换失败", e);
        }
    }

    // Clob转字符串工具方法
    private static String clobToString(Clob clob) throws SQLException {
        if (clob == null) return null;
        try (Reader reader = clob.getCharacterStream()) {
            StringBuilder sb = new StringBuilder();
            char[] buffer = new char[4096];
            int len;
            while ((len = reader.read(buffer)) != -1) {
                sb.append(buffer, 0, len);
            }
            return sb.toString();
        } catch (IOException e) {
            throw new SQLException("Clob转换失败", e);
        }
    }

    public static void closeConnection(Connection connection) {
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
